from sqlalchemy.orm import *
from sqlalchemy.types import *
from sqlalchemy import create_engine
from typing import Optional
from sqlalchemy import select
import rich
from .account import account
from .bill_type import bill_type
from .user import user
import datetime
from collections import defaultdict

class Base(DeclarativeBase):
    __abstract__ = True



class bill(Base):
    __tablename__ = "bill"
    bill_id: Mapped[Integer] = mapped_column(Integer, nullable=False, primary_key=True, autoincrement=True)
    bill_name: Mapped[String] = mapped_column(String, )
    bill_type_id: Mapped[Integer] = mapped_column(Integer, )
    bill_number: Mapped[Float] = mapped_column(Float, )
    bill_target: Mapped[String] = mapped_column(String, )
    bill_created_time: Mapped[String] = mapped_column(String, )
    bill_ext: Mapped[String] = mapped_column(String, )
    bill_account_id :Mapped[Integer] = mapped_column(Integer,)
    bill_user_id:Mapped[Integer] = mapped_column(Integer,)
    bill_type: Mapped[String] = mapped_column(String,)
    bill_group_id: Mapped[Integer] = mapped_column(Integer,)


def vis(engine,user_group_id):
    try:
        user_group_id = user_group_id #cond["user_group_id"]
        with Session(engine) as session:
            usersList = session.query(user).all()
            user_id_name_map = dict()
            for u in usersList:
                u = u._asdict()
                if u['user_group_id'] == user_group_id:
                    user_id_name_map[u['user_id']] = u['username']
        print(user_id_name_map)
        with Session(engine) as session: 
            group_num = len(session.query(user).filter_by(user_group_id=user_group_id).all())
        with Session(engine) as session:
            all_bill = session.query(bill).filter_by(bill_group_id=user_group_id).all()
        # get today bills
        today_bills = []
        bill_daily = defaultdict(int)
        for b in all_bill:
            print(b.bill_created_time,b.bill_number)
            if datetime.datetime.now().strftime("%Y-%m-%d") in str(b.bill_created_time):
                today_bills.append(b)
            t = str(b.bill_created_time).split(" ")[0]
            bill_daily[t] += int(b.bill_number)
        if len(bill_daily) == 0:
            min_date = (datetime.datetime.now()- datetime.timedelta(days=30)).strftime("%Y-%m-%d")
        else:
            min_date = min(list(bill_daily.keys()))
        max_date = datetime.datetime.now().strftime("%Y-%m-%d")
        date_delta = (datetime.datetime.strptime(max_date,"%Y-%m-%d") - datetime.datetime.strptime(min_date,"%Y-%m-%d")).days
        for i in range(date_delta):
            date = (datetime.datetime.strptime(min_date,"%Y-%m-%d") + datetime.timedelta(days=i)).strftime("%Y-%m-%d")
            if date not in bill_daily.keys():
                bill_daily[date] = 0
        today_res = {"income":0,"outcome":0}
        today_per_person = defaultdict(int)
        for k,v in user_id_name_map.items():
            today_per_person[v] = 0
        for b in today_bills:
            today_per_person[user_id_name_map[b.bill_user_id]] += b.bill_number if b.bill_number > 0 else -1*b.bill_number
            if b.bill_number > 0:
                today_res["income"] += b.bill_number
            else:
                today_res["outcome"] += -1* b.bill_number

        bill_daily = [{"date":k,"number":v} for k,v in bill_daily.items()]
        bill_daily = sorted(bill_daily,key=lambda x:x["date"])

        today_person = []
        for k,v in today_per_person.items():
            today_person.append({"name":k,"number":v})

        return {"group_num":group_num,"today_bills":today_res,"bill_daily":bill_daily,"today_per_person":today_person}
    except Exception as e:
        print(e)
        print(e.with_traceback())
        return e

    




def select_all(engine):
    with Session(engine) as session:
        return [rec._asdict() for rec in session.query(bill).all()]


def select_by_col(engine, cond: dict):
    try:
        with Session(engine) as session:
            return [rec._asdict() for rec in session.query(bill.__table__,bill_type.__table__,account.__table__,user.__table__).filter_by(**cond).
            join(bill_type, bill.bill_type_id == bill_type.billtype_id)
            .join(account,bill.bill_account_id==account.account_id)
            .join(user,user.user_id==bill.bill_user_id).all()]
    except Exception as e:
        print(e)

def update_one(engine, cond: dict, item: dict):
    with Session(engine) as session:
        record = session.query(bill).filter_by(**cond).first()
        if record:
            # Update the record with the new values
            for key, value in item.items():
                setattr(record, key, value)
            session.commit()
            return True
        return False


def add_one(engine, item: dict):
    try:
        with Session(engine) as session:
            record = bill(**item)
            session.add(record)
            session.commit()
            return True
    except Exception as e:
        rich.print(f"[red] {e}")
        return False


def delete_one(engine, cond: dict):
    try:
        with Session(engine) as session:
            record = session.query(bill).filter_by(**cond).first()
            session.delete(record)
            session.commit()
            return True
    except Exception as e:
        rich.print(f"[red] {e}")
        return False


def delete_batch(engine, cond: dict):
    try:
        with Session(engine) as session:
            records = session.query(bill).filter_by(**cond).all()
            for rec in records:
                session.delete(rec)
            session.commit()
            return True
    except Exception as e:
        rich.print(f"[red] {e}")
        return False
